﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Windows;
using System.Data.OleDb;

namespace AttentenceCheckSystem
{
    class ExcelImporter
    {
        public void Import(DataTable dataTable)
        {
            List<DateTime> tempSet = new List<DateTime>();
            string strConn = Properties.Settings.Default.Database1ConnectionString;
            try
            {
                using (System.Data.OleDb.OleDbConnection conn = new System.Data.OleDb.OleDbConnection(strConn))
                {
                    conn.Open();
                    foreach (DataRow row in dataTable.Rows)
                    {
                        int uid = 0;
                        try
                        {
                            uid = Convert.ToInt32(row["工号"]);
                        }
                        catch(System.Exception)
                        {
                            continue;
                        }
                        
                        DateTime checkTime = Convert.ToDateTime(row["时间"]);
                        DateTime actualDate = checkTime.AddHours(-6).Date;
                        System.Data.OleDb.OleDbCommand command = conn.CreateCommand();
                        command.CommandText = "SELECT * FROM 打卡记录 WHERE uid=@uid AND 日期=@date";
                        command.Parameters.Add("@uid", OleDbType.Integer).Value = uid;
                        command.Parameters.Add("@date", OleDbType.DBDate).Value = actualDate;
                        OleDbDataReader reader = command.ExecuteReader();
                        if (reader.HasRows)
                        {
                            reader.Read();

                            tempSet.Clear();
                            if (!tempSet.Contains(checkTime))
                                tempSet.Add(checkTime);
                            DateTime tempDateTime = Convert.ToDateTime(reader["记录1"]);
                            if (!tempSet.Contains(tempDateTime))
                                tempSet.Add(tempDateTime);
                            if (!(reader["记录2"] is DBNull))
                            {
                                tempDateTime = Convert.ToDateTime(reader["记录2"]);
                                if (!tempSet.Contains(tempDateTime))
                                    tempSet.Add(tempDateTime);
                            }
                            tempSet.Sort();
                            reader.Close();
                            command.CommandText = "UPDATE 打卡记录 SET 打卡记录.记录1 = @record1, 打卡记录.记录2 = @record2 WHERE uid=@uid AND 日期=@date";
                            command.Parameters.Clear();
                            command.Parameters.Add("@record1",OleDbType.Date).Value = tempSet[0];
                            command.Parameters.Add("@record2", OleDbType.Date).Value = tempSet[tempSet.Count - 1];
                            command.Parameters.Add("@uid", OleDbType.Integer).Value = uid;
                            command.Parameters.Add("@date", OleDbType.Date).Value = actualDate;
                            int i = command.ExecuteNonQuery();
                        }
                        else
                        {
                            reader.Close();
                            command.CommandText = "INSERT INTO 打卡记录 (uid, 日期, 记录1) VALUES(@uid, @date, @record)";
                            //command.Parameters.Clear();
                            //command.Parameters.Add("@uid", OleDbType.Integer).Value = uid;
                            //command.Parameters.Add("@date", OleDbType.Date).Value = actualDate;
                            command.Parameters.Add("@record", OleDbType.Date).Value = checkTime;
                            string str = command.ToString();
                            command.ExecuteNonQuery();
                        }
                    }
                    PostImport(conn);
                    MessageBox.Show("导入数据完毕", "信息", MessageBoxButton.OK, MessageBoxImage.Information);
                }
            }
            catch (System.Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }

        public void PostImport(System.Data.OleDb.OleDbConnection conn)
        {
            DataSet dataSet = new DataSet();

            OleDbDataAdapter UncalculatedOverworkAdapter = new OleDbDataAdapter("SELECT * FROM 未计算的加班申请VIEW", conn);
            UncalculatedOverworkAdapter.Fill(dataSet, "未计算的加班申请VIEW");

            Database1DataSetTableAdapters.holidaysTableAdapter HolidaysAdapter = new Database1DataSetTableAdapters.holidaysTableAdapter();
            Database1DataSet.holidaysDataTable HolidaysTable = HolidaysAdapter.GetData();

            Database1DataSetTableAdapters.加班申请TableAdapter ApplyOverworkAdapater = new Database1DataSetTableAdapters.加班申请TableAdapter();
            Database1DataSet.加班申请DataTable ApplyOverworkTable = ApplyOverworkAdapater.GetData();

            DataTable UncalculatedOverworkTable = dataSet.Tables["未计算的加班申请VIEW"];
            
            foreach(DataRow row in UncalculatedOverworkTable.Rows)
            {
                DateTime startTime = Convert.ToDateTime(row["记录1"]);
                DateTime endTime = Convert.ToDateTime(row["记录2"]);
                TimeSpan OverworkTime = new TimeSpan(endTime.Ticks - startTime.Ticks);
                if (OverworkTime.TotalHours >= 1)
                {
                    int uid = Convert.ToInt32(row["uid"]);
                    DateTime date = Convert.ToDateTime(row["日期"]);
                    
                    DataRow [] rows = HolidaysTable.Select(
                        string.Format("uid={0} AND 类型=2 AND 月份=#{1}#", uid, 
                                       common.GetCheckStartDate(date).ToString()));
                    if (rows.Length == 0)
                    {
                        DataRow newRow = HolidaysTable.NewRow();
                        newRow["uid"] = uid;
                        newRow["类型"]=2;
                        newRow["月份"] = common.GetCheckStartDate(date);
                        newRow["时长"] = Convert.ToInt32(OverworkTime.TotalHours);
                        HolidaysTable.Rows.Add(newRow);
                    }
                    else
                    {
                        rows[0]["时长"] = Convert.ToInt32(rows[0]["时长"]) + Convert.ToInt32(OverworkTime.TotalHours);
                    }

                    rows = ApplyOverworkTable.Select(string.Format("uid={0} AND 日期=#{1}#", uid, date.ToString()));
                    if (rows.Length > 0)
                    {
                        rows[0]["Calculated"] = 1;
                    }
                }
            }
            HolidaysAdapter.Update(HolidaysTable);

            ApplyOverworkAdapater.Update(ApplyOverworkTable);
        }
    }
}
